package com.lw.oop.dayfive.cdemo;

import java.sql.*;

/**
 * @author liuwei
 * @ClassName EmpJdbbc.java
 * @description: TODO
 * @date 2023年06月20日
 * @version: 1.0
 */
public class EmpJdbbc {
    static Statement statement = null;
    static Connection conn;
    static PreparedStatement pstmt = null;
    public void jdbc() throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String pas = "root";
        //获取执行者
        conn = DriverManager.getConnection(url, user, pas);
        if (conn != null) {
            System.out.println("连接成功");
            //获取发送 SQL 的对象
            statement = conn.createStatement();
        }else {
            System.out.println("连接失败");
        }
    }

    /**
     * 名字的第三个字母为a的员工信息，将查询到的信息逐行打印
     * @throws SQLException
     */
    public void selectEmp() throws SQLException {
        if (conn != null) {
            String sql2="select * from employees where empName like '__a%'";
            ResultSet result2 = statement.executeQuery(sql2);
            //处理结果集
            while(result2.next()){
                //5.1有数据，依据列名获取数据
                Integer id = result2.getInt("empid");
                String name=result2.getString("empname");
                String job=result2.getString("job");
                System.out.println("员工编号："+id+"员工姓名："+name+"职业："+job);
            }
        }
    }


    /**
     * 查询部门编号为30或者工资大于2000的员工信息。将查询到的信息逐行打印
     * @throws SQLException
     */
    public void selectEmp1() throws SQLException {
        if (conn != null) {
            String sql2="select * from employees where topid=1 and bonus>2000";
            ResultSet result2 = statement.executeQuery(sql2);
            //处理结果集
            while(result2.next()){
                //有数据，依据列名获取数据
                Integer id = result2.getInt("empid");
                String name=result2.getString("empname");
                String job=result2.getString("job");
                System.out.println("员工编号："+id+"员工姓名："+name+"职业："+job);
            }
        }
    }



    /**
     * 在emp表中，使用in关键字查询职务为”president”,”manager”和”analyst”中任意一种的员工信息。将查询到的信息逐行打印
     * @throws SQLException
     */
    public void selectEmp2() throws SQLException {
        if (conn != null) {
            String sql2="select * from employees where job in('Java开发','运维工程师')";
            ResultSet result2 = statement.executeQuery(sql2);
            //处理结果集
            while(result2.next()){
                //有数据，依据列名获取数据
                Integer id = result2.getInt("empid");
                String name=result2.getString("empname");
                String job=result2.getString("job");
                System.out.println("员工编号："+id+"员工姓名："+name+"职业："+job);
            }
        }
    }

    /**
     * 求部门编号20中员工的平均工资，工资总和，工资最大值，最小值，人数。将查询到信息打印
     * @throws SQLException
     */
    public void selectEmp3() throws SQLException {
        if (conn != null) {
            String sql2="select avg(bonus) avg,sum(bonus) sum,max(bonus) max," +
                    "min(bonus) min,count(*) count from employees where topid=1";
            ResultSet result2 = statement.executeQuery(sql2);
            //处理结果集
            while(result2.next()){
                //有数据，依据列名获取数据
                Integer avg = result2.getInt("avg");
                Integer sum = result2.getInt("sum");
                Integer max = result2.getInt("max");
                Integer min = result2.getInt("min");
                Integer count = result2.getInt("count");
                System.out.println("平均工资："+avg+"\n工资总和："+sum+"\n工资最大值："+max
                        +"\n工资最小值:"+min+"\n人数:"+count);
            }
        }
    }

    /**
     * 关闭所有
     *
     * @throws SQLException sqlexception异常
     */
    public void closeAll() throws SQLException {
        if (statement!=null){
            statement.close();
        }
        if (conn!=null){
            conn.close();
        }
        if (pstmt!=null){
            pstmt.close();
        }
    }
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        EmpJdbbc e1=new EmpJdbbc();
        e1.jdbc();
        e1.selectEmp();
        System.out.println("********************************");
        e1.selectEmp1();
        System.out.println("**********************************");
        e1.selectEmp2();
        System.out.println("**********************************");
        e1.selectEmp3();
        e1.closeAll();
    }

}
